package com.gmail.blackdog1987.onedollar.dao;

import com.gmail.blackdog1987.onedollar.dao.hibernate.EntityHibernateDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.BaseJdbcDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.JdbcUtil;
import com.gmail.blackdog1987.onedollar.entity.*;
import com.gmail.blackdog1987.onedollar.entity.base.Page;
import com.gmail.blackdog1987.onedollar.vo.GoodsJudgeVO;
import com.gmail.blackdog1987.onedollar.vo.GoodsVO;
import com.gmail.blackdog1987.onedollar.vo.OrderVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository("goodsDao")
public class GoodsDao extends EntityHibernateDao<TGoodsEntity>{
    @Autowired
    private BaseJdbcDao baseJdbcDao;

    public Page<GoodsVO> queryForPage(int pageindex, int pagesize, int category, String sort, String orderby, String keywords){
        String whereSql;
        ArrayList<Object> whereArgs = new ArrayList<Object>();
        whereSql = " FROM t_goods t\n" +
                "LEFT JOIN t_goods_picture pic ON t.goods_id = pic.goods_id\n" +
                "AND pic.is_main=1 WHERE t.apply_status=1 AND is_shelves=1 ";
        if(category >=0 ){
            whereSql += " AND t.cat_id = ? ";
            whereArgs.add(Integer.valueOf(category));
        }

        if(keywords!=null&&keywords.length()!=0){
            whereSql += " AND t.keywords like ? ";
            whereArgs.add("%" + keywords + "%");
        }

        String selectionSql =
                " SELECT t.*,\n" +
                "       pic.`pic_url` ";

        if(sort.equals("0")){
            whereSql += " ORDER BY t.soldnumber ";
        }else if(sort.equals("1")){
            whereSql += " ORDER BY t.default_price ";
        }else{
            whereSql += " ORDER BY t.sort_order ";
        }

        if(orderby.equals("0")){
            whereSql += " DESC ";
        }

        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , whereArgs.toArray());

        List<GoodsVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsVO.class, selectionSql + whereSql + limit, whereArgs.toArray());

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

    public Page<GoodsVO> queryForPage(int pageindex, int pagesize, String sort, String orderby, String keyword){
        return queryForPage(pageindex, pagesize, -1, sort, orderby, keyword);
    }

    public Page<OrderVO> queryForPageByUserId(int pageindex, int pagesize, int userid){
        String whereSql =
                " FROM t_order_info\n" +
                        "left join t_order_goods on t_order_info.order_id = t_order_goods.order_id\n" +
                        "left join t_goods_picture on t_order_goods.goods_id = t_goods_picture.goods_id and t_goods_picture.is_main = 1\n" +
                        "WHERE t_order_goods.order_id = t_order_info.order_id\n" +
                        "  AND t_order_info.user_id = ?\n" +
                        "  AND t_order_info.pay_status = 1\n" +
                        "  ORDER BY t_order_info.add_time DESC";

        String selectionSql =
                " SELECT t_order_goods.*,\n" +
                        "       t_order_info.*,\n" +
                        "       t_goods_picture.pic_url ";

        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , new Object[]{userid});

        List<OrderVO> list = JdbcUtil.queryForList(baseJdbcDao, OrderVO.class, selectionSql + whereSql + limit, new Object[]{userid});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

    public List<TGoodsParamEntity> queryParams(int goodsid){

        String selectionSql = "SELECT *\n" +
                "FROM t_goods_param p\n" +
                "WHERE p.cat_id =\n" +
                "    (SELECT cat_id\n" +
                "     FROM t_goods g\n" +
                "     WHERE g.goods_id = ?)";

        return JdbcUtil.queryForList(baseJdbcDao, TGoodsParamEntity.class, selectionSql, new String[]{String.valueOf(goodsid)});
    }

    public List<TGoodsEntity> queryByIds(String ids){

        String selectionSql = "SELECT *\n" +
                "FROM t_goods where goods_id in (?)";

        return JdbcUtil.queryForList(baseJdbcDao, TGoodsEntity.class, selectionSql, new String[]{String.valueOf(ids)});
    }

    public List<TParamGroupEntity> queryParamsValues(int goodsid){

        String selectionSql = "SELECT *\n" +
                "                FROM t_param_group\n" +
                "                WHERE goods_id = ?\n" +
                "                ORDER BY param_id";

        return JdbcUtil.queryForList(baseJdbcDao, TParamGroupEntity.class, selectionSql, new String[]{String.valueOf(goodsid)});
    }

    public List<GoodsJudgeVO> queryComments(int goodsid, int limit){

        String selectionSql =
                "SELECT t_goods_judge.*,\n" +
                "       t_user.nick_name,\n" +
                "       t_user.head\n" +
                "FROM t_goods_judge\n" +
                "LEFT JOIN t_user ON t_user.user_id = t_goods_judge.user_id\n" +
                "WHERE t_goods_judge.goods_id = ?\n" +
                "ORDER BY t_goods_judge.add_time DESC LIMIT ?";

        return JdbcUtil.queryForList(baseJdbcDao, GoodsJudgeVO.class, selectionSql, new Object[]{String.valueOf(goodsid), limit});
    }

    public TGoodsEntity queryOneDollar(int onedollarid){

        String selectionSql =
                "select t.* from t_goods t, t_goods_onedollar o where t.goods_id = o.goods_id and o.id = ?";

        List<TGoodsEntity> result = JdbcUtil.queryForList(baseJdbcDao, TGoodsEntity.class, selectionSql, new Object[]{onedollarid});

        return result==null||result.size()==0?null:result.get(0);
    }
}
